import numpy as np
import plotly.graph_objects as go
import plotly.express as px
import pandas as pd
import sys
sys.path.insert(1,'../')
from scripts.plotting_utils import *

df = pd.read_feather("../data/processed_data.feather")

non_generation = ['dt', 'price', 'demand_forecast', 'demand', 'wind_offshore_forecast', 'solar_forecast', 'wind_forecast', 'wind_onshore_forecast', 'doy', 'doy_season', 'residual', 'vre_forecast', 'hour']

from dotenv import load_dotenv
import os
import entsoe
def get_entsoe_client():
    """Initializes and returns the EntsoePandasClient."""
    load_dotenv()
    API_KEY = os.environ.get("ENTSOE_API_KEY")
    if not API_KEY:
        raise ValueError(
            "ENTSOE_API_KEY environment variable not set. Please set it in your .env file."
        )

    client = entsoe.EntsoePandasClient(api_key=API_KEY)
    return client

client = get_entsoe_client()

start = min(df.DE_LU.index)
end = max(df.DE_LU.index)
start, end

(Timestamp('2025-08-16 22:00:00+0000', tz='UTC'),
 Timestamp('2025-08-24 22:00:00+0000', tz='UTC'))

country_code = 'FR'

df_imports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= False, per_hour = False).drop(columns='sum')

# df_imports = df_imports.resample(rule='h').mean()

import_sum = df_imports.ffill().transpose().sum()
import_sum.name='imports'

# df_exports = client.query_physical_crossborder_allborders(country_code='FR', start=start, end=end, export= True, per_hour = True)
df_exports = client.query_physical_crossborder_allborders(country_code='FR', start=start, end=end, export= True, per_hour = False).drop(columns='sum')

df_exports = df_exports.ffill()
df_exports.plot()

df_exports

df_exports.CH.ffill().plot()

df_exports.CH.plot(kind='scatter')

export_sum = df_exports.ffill().transpose().sum()
export_sum.name='exports'

df_phs = client.query_generation(country_code, start=start, end=end, psr_type='B10')

idx = pd.IndexSlice

# phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']].resample(rule='h').mean()
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption.columns = phs_consumption.columns.droplevel(level=1)

# dfFR = pd.concat([import_sum, -1 * export_sum, df.FR.drop(columns=non_generation), -1* phs_consumption], axis=1)
dfFR = pd.concat([import_sum, -1 * export_sum, df.FR.drop(columns=non_generation)], axis=1)
# dfFR = pd.concat([-1 * export_sum, df.FR.drop(columns=non_generation), -1* phs_consumption], axis=1)

dfFR

df_net_demand = pd.concat([df.FR.demand,phs_consumption],axis=1)
df_net_demand['net_demand'] = df_net_demand.demand+df_net_demand['Hydro Pumped Storage']
df_net_demand

fig = dfFR.plot(kind='area')
fig.add_trace(go.Scatter(y=df_net_demand.net_demand, x=df.FR.dt, mode='lines',line=dict(color='black')))
fig.add_trace(go.Scatter(y=df.FR.demand, x=df.FR.dt, mode='lines',line=dict(color='gray')))
# fig.add_trace(go.Scatter(y=import_sum, x=import_sum.index, stackgroup='1', mode='lines'))
# fig.add_trace(go.Scatter(y=df.FR.demand_forecast, x=df.FR.dt, mode='lines',line=dict(color='gray')))

df_phs

country_code = 'DE_LU'
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption

country_code = 'FR'
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption

country_code = 'DE_LU'
df_imports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= False, per_hour = False)
df_exports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= True, per_hour = False)
import_sum = df_imports.ffill().drop(columns='sum').transpose().sum()
import_sum.name='imports'
export_sum = df_exports.ffill().drop(columns='sum').transpose().sum()
export_sum.name='exports'
# df_phs = client.query_generation(country_code, start=start, end=end, psr_type='B10')
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption.columns = phs_consumption.columns.droplevel(level=1)
dfDE = pd.concat([-1 * export_sum, -1* phs_consumption, import_sum, df.DE_LU], axis=1)
dfDE

# fig = df_exports.ffill().drop(columns='sum').transpose().sum().plot()

# data = df_exports.drop(columns='sum').transpose().sum().plot().data[0]
# data.line.color = 'red'
# fig.add_trace(data)

fig = dfDE.drop(columns=non_generation).plot(kind='area')
fig.add_trace(go.Scatter(y=df.DE_LU.demand, x=df.DE_LU.dt, mode='lines',line=dict(color='black')))
# fig.add_trace(go.Scatter(y=import_sum, x=import_sum.index, stackgroup='1', mode='lines'))
fig.add_trace(go.Scatter(y=df.DE_LU.demand_forecast, x=df.DE_LU.dt, mode='lines',line=dict(color='gray')))

country_code = 'FR'
df_imports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= False, per_hour = False)
df_exports = client.query_physical_crossborder_allborders(country_code=country_code, start=start, end=end, export= True, per_hour = False)
import_sum = df_imports.ffill().drop(columns='sum').transpose().sum()
import_sum.name='imports'
export_sum = df_exports.ffill().drop(columns='sum').transpose().sum()
export_sum.name='exports'
# df_phs = client.query_generation(country_code, start=start, end=end, psr_type='B10')
df_phs = client.query_generation(country_code, start=start, end=end)
phs_consumption = df_phs.loc[idx[:],idx[:,'Actual Consumption']]
phs_consumption.columns = phs_consumption.columns.droplevel(level=1)
df_plot = pd.concat([-1 * export_sum, -1* phs_consumption, import_sum, df[country_code]], axis=1)
df_plot

fig = df_plot.drop(columns=non_generation).plot(kind='area')
fig.add_trace(go.Scatter(y=df[country_code].demand, x=df[country_code].dt, mode='lines',line=dict(color='black')))
# fig.add_trace(go.Scatter(y=import_sum, x=import_sum.index, stackgroup='1', mode='lines'))
fig.add_trace(go.Scatter(y=df[country_code].demand_forecast, x=df[country_code].dt, mode='lines',line=dict(color='gray')))

start = pd.Timestamp('20240630', tz='Europe/Brussels')
end = pd.Timestamp('20250630', tz='Europe/Brussels')
period = slice(start,end)

# df_FR = df.FR.loc[period]
df_FR = df.FR
data = []
data.append(
    go.Scatter(
        x=df_FR.index,
        y=df_FR.demand,
        line=dict(
            width=0.5,
            color=light_blue_gray,
        ),
        showlegend=False,
    )
)
data.append(
    go.Scatter(
        x=df_FR.index,
        y=df_FR.demand.rolling(window=24 * 7*4, center=True, min_periods=24*7).mean(),
        line=dict(
            color=fca_blue,
        ),
        name="1 month moving average",
    )
)

layout = go.Layout(
    template=fca_template,
    title_text="Electricity consumption France (MW)",
    yaxis_range=[0,89e3]
)
fig = go.Figure(data=data, layout=layout)
fig.show()

layout = go.Layout(
    template=fca_template,
    title_text="Electricity consumption over one week, France (MW)",
    yaxis_range=[0,89e3],
    xaxis_range=[
        pd.Timestamp('2024-08-18 22:00:00'),
        pd.Timestamp('2024-08-26 4:00:00')
    ]
)
fig = go.Figure(data=data, layout=layout)
fig.show()

px.density_heatmap(
    df_FR,
    x="hour",
    y="demand",
    # y="weighted_market_val",
    nbinsy=60,
    nbinsx=24,
    color_continuous_scale=cm_data_plotly,
).update_layout(template=fca_template, title_text='Frequency of demand by hour, France (green-yellow = most frequent values)')

start = pd.Timestamp('20240101', tz='Europe/Brussels')
end = pd.Timestamp('20250101', tz='Europe/Brussels')

period = slice(start,end)
df_DE = df.DE_LU.loc[period]
df_DE.price.mean()

nan

start = pd.Timestamp('20240817', tz='Europe/Brussels')
end = pd.Timestamp('20250817', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]
# 0.109 is the capital recovery factor

avgs = []
for q in np.linspace(0,1,500):
    quant = df_DE.price.quantile(q)
    mean = df_DE.query('price <= @quant').price.mean()
    # avgs.append(mean if mean >= 0 else 0)
    avgs.append(mean)
avgs = np.array(avgs)
fig = go.Figure()
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs / 1.0, # 100% efficiency, roughly cancels out with gas price being GCV ~50/55?
        name="average Electricity cost 1MWh_heat",
        line_color="black",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="1000 EUR/kW",
        line_color=blue_gray,
        line_dash="dot",
    )
)
# fig.add_trace(go.Scatter(x=np.linspace(1/500,1,100), y=500e3*0.109/(365*24*np.linspace(1/500,1,100)), name='500 EUR/kW' ))
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="500 EUR/kW",
        line_color=dark_gray,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="250 EUR/kW",
        line_color=fca_blue,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=100e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="100 EUR/kW",
        line_color=highlight_blue,
        line_dash="dot",
    )
)

fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs 
        + 1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        showlegend=False,
        line_color=blue_gray,
    )
)

fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs 
        + 500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        showlegend=False,
        line_color=dark_gray,
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs 
        + 250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="LCoHeat for respective capital cost",
        line_color=fca_blue,
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs 
        + 100e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        showlegend=False,
        line_color=highlight_blue,
    )
)
# fig.add_trace(go.Scatter(x=np.linspace(1/500,1,500), y=df_DE.price.quantile(np.linspace(1/500,1,500)) + 500e3*0.109/(365*24*np.linspace(1/500,1,500)),name='LCoH'))
fig.update_layout(
    dict(
        width=800,
        title="LCOHeat for Germany, 01/2024 - 08/2025<br> as a function of production/charging at cheapest X percent of price hours",
        xaxis_title="price hour percentile",
        yaxis_title="eur/MWh",
    )
)
fig.update_layout(template=fca_template, yaxis_range=[-25,110], xaxis_range=[0,1.19], legend=dict(x=1, y=0.6))
fig.show()

start = pd.Timestamp('20240101', tz='Europe/Brussels')
end = pd.Timestamp('20250101', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]
df_DE.price.mean()

nan

start = pd.Timestamp('20240817', tz='Europe/Brussels')
end = pd.Timestamp('20250817', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]

# 0.109 is the capital recovery factor

avgs = []
for q in np.linspace(0,1,500):
    quant = df_DE.price.quantile(q)
    mean = df_DE.query('price <= @quant').price.mean()
    # avgs.append(mean if mean >= 0 else 0)
    avgs.append(mean)
avgs = np.array(avgs)
fig = go.Figure()

# variable cost
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs / 0.73, # 73% efficiency lhv
        name="average Electricity cost 1MWh_H2_lhv 72% eff.",
        line_color="black",
    )
)

# indicate 2 eur/kg price level
fig.add_trace(
    go.Scatter(
        x=np.linspace(0,1,100),
        y=[60.024,60.024]*50, # 2 eur/kg /(33.32kWh/kg) in eur/MWh = 60.024 EUR / MWh
        name="2 EUR/kg_H2",
        line_color="black",
        mode='lines',
        line_dash='dot',
    )
)

# fixed cost from capital (+O&M, implicitly)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="2500 EUR/kW",
        line_color=highlight_blue,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="1000 EUR/kW",
        line_color=blue_gray,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="500 EUR/kW",
        line_color=dark_gray,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="250 EUR/kW",
        line_color=fca_blue,
        line_dash="dot",
    )
)

# variable cost added to fixed
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        showlegend=False,
        name="2500 EUR/kW",
        line_color=highlight_blue,
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="1000 EUR/kW",
        showlegend=False,
        line_color=blue_gray,
    )
)

fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="500 EUR/kW",
        showlegend=False,
        line_color=dark_gray,
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="LCoH for capital cost (four values)",
        line_color=fca_blue,
    )
)



fig.update_layout(
    dict(
        width=800,
        title="LCOHydrogen for Germany, 08/2024 - 08/2025<br> as a function of production/charging at cheapest X percent of price hours",
        xaxis_title="price hour percentile",
        yaxis_title="eur/MWh",
    )
)
fig.update_layout(template=fca_template, yaxis_range=[-25,150], xaxis_range=[0,1.24], legend=dict(x=1.8, y=0.6))
fig.show()

# trying with clamped prices

start = pd.Timestamp('20240817', tz='Europe/Brussels')
end = pd.Timestamp('20250817', tz='Europe/Brussels')
period = slice(start,end)
df_DE = df.DE_LU.loc[period]

# 0.109 is the capital recovery factor

avgs = []
for q in np.linspace(0,1,500):
    quant = df_DE.price.quantile(q)
    mean = df_DE.query('price <= @quant').price.mean()
    avgs.append(mean if mean >= 0 else 0)
    #avgs.append(mean)
avgs = np.array(avgs)
fig = go.Figure()

# variable cost
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs / 0.73, # 73% efficiency lhv
        name="average Electricity cost 1MWh_H2_lhv 72% eff.",
        line_color="black",
    )
)

# indicate 2 eur/kg price level
fig.add_trace(
    go.Scatter(
        x=np.linspace(0,1,100),
        y=[60.024,60.024]*50, # 2 eur/kg /(33.32kWh/kg) in eur/MWh = 60.024 EUR / MWh
        name="2 EUR/kg_H2",
        line_color="black",
        mode='lines',
        line_dash='dot',
    )
)

# fixed cost from capital (+O&M, implicitly)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="2500 EUR/kW",
        line_color=highlight_blue,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="1000 EUR/kW",
        line_color=blue_gray,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="500 EUR/kW",
        line_color=dark_gray,
        line_dash="dot",
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="250 EUR/kW",
        line_color=fca_blue,
        line_dash="dot",
    )
)

# variable cost added to fixed
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 2500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        showlegend=False,
        name="2500 EUR/kW",
        line_color=highlight_blue,
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 1000e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="1000 EUR/kW",
        showlegend=False,
        line_color=blue_gray,
    )
)

fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 500e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="500 EUR/kW",
        showlegend=False,
        line_color=dark_gray,
    )
)
fig.add_trace(
    go.Scatter(
        x=np.linspace(1/500, 1, 500),
        y=avgs /0.73
        + 250e3 * 0.109 / (365 * 24 * np.linspace(1/500, 1, 500)),
        name="LCoH for capital cost (four values)",
        line_color=fca_blue,
    )
)



fig.update_layout(
    dict(
        width=800,
        title="LCOHydrogen for Germany, 08/2024 - 08/2025<br> as a function of production/charging at cheapest X percent of price hours",
        xaxis_title="price hour percentile",
        yaxis_title="eur/MWh",
    )
)
fig.update_layout(template=fca_template, yaxis_range=[-25,150], xaxis_range=[0,1.24], legend=dict(x=1.8, y=0.6))
fig.show()

df_EU = df
# # only 202X
y0 = 2022
y1 = 2023
# period = slice(f'{y0}-01-01 00:00:00',f'{y1}-01-01 00:00:00')
df_DE_2 = df_EU.DE_LU.copy()

y0, y1 = 2023, 2024
df23 = (df_DE_2
    .copy()
    .loc[slice(f'{y0}-01-01 00:00:00', f'{y1}-01-01 00:00:00')]
    .assign(vre = lambda df: df.vre_forecast)
    .assign(wind = lambda df: df.wind_forecast)
    .assign(solar = lambda df: df.solar_forecast)
    .assign(market_val = lambda df: df.price * df.vre / df.demand)
    .assign(vre_quant = lambda df: df.vre/df.vre.max())
)
vre_mean = df23.vre.mean()
vre_quant = df23.vre.quantile(0.75)
bin_width = 1000
df_DE_2 = df23.copy()

df_DE_2_vre = df_DE_2.assign(vre_bin=lambda df: np.floor((df.vre / bin_width))*bin_width).groupby('vre_bin').price.describe()
df_DE_2_means = df_DE_2.assign(vre_bin=lambda df: np.floor((df.vre / bin_width))*bin_width).groupby('vre_bin').mean()
df_DE_2_median = df_DE_2.assign(vre_bin=lambda df: np.floor((df.vre / bin_width))*bin_width).groupby('vre_bin').median()
# df_DE_2_wind = df_DE_2.assign(wind_bin=lambda df: np.floor((df.wind / bin_width))*bin_width).groupby('wind_bin').mean()
df_DE_2_wind = df_DE_2.assign(wind_bin=lambda df: np.floor((df.wind / bin_width))*bin_width).groupby('wind_bin').price.describe()
# df_DE_2_wind['mean']
df_DE_2_wind.head()
# df_DE_2_solar = df_DE_2.assign(solar_bin=lambda df: np.floor((df.solar / bin_width))*bin_width).groupby('solar_bin').mean()
df_DE_2_solar = df_DE_2.assign(solar_bin=lambda df: np.floor((df.solar / bin_width))*bin_width).groupby('solar_bin').price.describe()
# df_DE_2_solar['mean']
df_DE_2_solar['25%']

fig = go.Figure()

scatter = go.Scatter(
    x=df_DE_2.vre,
    y=df_DE_2.market_val,
    mode='markers',
    marker=dict(opacity=0.1, size=3, color=gray),
    name='Hourly market value (VRE production × day ahead price)',
    # y="weighted_market_val",
    # nbinsy=3000,
    # nbinsx=1000,
    # color_continuous_scale="magma",
)
fig.update_layout(
    template=fca_template,
    yaxis_range=[-0.34e6, 4.2e6],
    xaxis_range=[-1000, 62e3],
    yaxis_title_text='Market value (€)',
    xaxis_title_text='VRE production (MW)',
    title=f"Market value of VREs declining with rising share of generation in Germany from {y0} to {y1}",
    legend=dict(
        x=1.1,
        y=1,
        xanchor='left',
        yanchor='top',
    ),
    height=540*1.4,
    width=960*1.4,
)
# fig.add_trace(scatter)
fig.add_trace(
    go.Scatter(
        x=[0, df_DE_2.vre.max()],
        y=[0, df_DE_2.price.mean() * df_DE_2.vre.max()],
        mode="lines",
        line_color=very_dark_gray,
        line_width=4,
        name=f'VRE production × Mean price {y0}-{y1}'
    )
)
fig.add_vline(x=df_DE_2.demand.mean(), annotation=dict(text='mean demand'))

green = 'rgba(145, 192, 150, 1)'
fig.add_trace(
    go.Scatter(
        x=df_DE_2_means.index,
        y=df_DE_2_means.price*df_DE_2_means.index,
        mode="lines",
        line_color=green,
        line_width=4,
        name='Mean VRE market value'
    )
)


green = 'rgba(145, 192, 150, 0.2)'
fig.add_traces(
    go.Scatter(
        x=df_DE_2_vre["25%"].index,
        y=df_DE_2_vre["25%"] * df_DE_2_vre["25%"].index,
        line=dict(color=green),
        name='IQR',
    )
)
fig.add_traces(
    go.Scatter(
        x=df_DE_2_vre["75%"].index,
        y=df_DE_2_vre["75%"] * df_DE_2_vre["75%"].index,
        fill="tonexty",
        fillcolor=green,
        line=dict(color=green),
        showlegend=False,
    )
)

sand_yellow = 'rgba(226, 182, 129, 1)'
fig.add_trace(
    go.Scatter(
        x=df_DE_2_solar.index,
        y=df_DE_2_solar['mean']*df_DE_2_solar.index,
        mode="lines",
        line_color=sand_yellow,
        line_width=4,
        name='Mean solar market value'
    )
)
sand_yellow = 'rgba(226, 182, 129, 0.2)'
fig.add_traces(
    go.Scatter(
        x=df_DE_2_solar["25%"].index,
        y=df_DE_2_solar["25%"] * df_DE_2_solar["25%"].index,
        line=dict(color=sand_yellow),
        name='IQR',
    )
)
fig.add_traces(
    go.Scatter(
        x=df_DE_2_solar["75%"].index,
        y=df_DE_2_solar["75%"] * df_DE_2_solar["75%"].index,
        fill="tonexty",
        fillcolor=sand_yellow,
        line=dict(color=sand_yellow),
        showlegend=False,
    )
)


highlight_blue = 'rgba(2, 147, 210, 1)'
fig.add_trace(
    go.Scatter(
        x=df_DE_2_wind.index,
        y=df_DE_2_wind['mean']*df_DE_2_wind.index,
        mode="lines",
        line_color=highlight_blue,
        line_width=4,
        name='Mean wind market value'
    )
)
highlight_blue = 'rgba(2, 147, 210, 0.2)'
fig.add_traces(
    go.Scatter(
        x=df_DE_2_wind["25%"].index,
        y=df_DE_2_wind["25%"] * df_DE_2_wind["25%"].index,
        line=dict(color=highlight_blue),
        name='IQR',
    )
)
fig.add_traces(
    go.Scatter(
        x=df_DE_2_wind["75%"].index,
        y=df_DE_2_wind["75%"] * df_DE_2_wind["75%"].index,
        fill="tonexty",
        fillcolor=highlight_blue,
        opacity=0.1,
        line=dict(color="rgba(0,0,0,0)"),
        showlegend=False,
    )
)

fig.show()

	BE	CH	DE_LU	ES	GB	IT_NORD
2025-08-17 00:00:00+02:00	2683.0	1215.0	3166.0	2705.0	4077.0	1232.0
2025-08-17 00:15:00+02:00	2618.0	1215.0	3073.0	2396.0	4077.0	1053.0
2025-08-17 00:30:00+02:00	2671.0	1215.0	3147.0	2407.0	4077.0	1046.0
2025-08-17 00:45:00+02:00	2712.0	1215.0	3169.0	2341.0	4077.0	1053.0
2025-08-17 01:00:00+02:00	2590.0	1133.0	2973.0	2351.0	4078.0	1033.0
...	...	...	...	...	...	...
2025-08-24 22:45:00+02:00	2074.0	47.0	2974.0	1626.0	3669.0	2793.0
2025-08-24 23:00:00+02:00	2324.0	1379.0	3066.0	1818.0	2908.0	3035.0
2025-08-24 23:15:00+02:00	2312.0	1379.0	3088.0	1752.0	2908.0	3047.0
2025-08-24 23:30:00+02:00	2251.0	1379.0	3028.0	1698.0	2908.0	3042.0
2025-08-24 23:45:00+02:00	2278.0	1379.0	2982.0	1716.0	2908.0	3075.0

	imports	exports	biomass	brown_coal	coal_gas	energy_storage	gas	geothermal	hard_coal	hydro_reservoir	...	oil	oil_shale	other	other_re	peat	pumped_storage	solar	waste	wind_offshore	wind_onshore
2025-08-16 22:00:00+00:00	0.0	-15078.0	228.0	0.0	0.0	3.0	1082.0	0.0	0.0	1286.0	...	69.0	0.0	0.0	0.0	0.0	2172.0	0.0	373.0	1375.0	5612.0
2025-08-16 22:15:00+00:00	0.0	-14432.0	234.0	0.0	0.0	3.0	1095.0	0.0	0.0	1184.0	...	69.0	0.0	0.0	0.0	0.0	994.0	0.0	373.0	1349.0	5485.0
2025-08-16 22:30:00+00:00	0.0	-14563.0	236.0	0.0	0.0	4.0	1006.0	0.0	0.0	922.0	...	69.0	0.0	0.0	0.0	0.0	530.0	0.0	372.0	1222.0	5476.0
2025-08-16 22:45:00+00:00	0.0	-14567.0	237.0	0.0	0.0	2.0	824.0	0.0	0.0	825.0	...	70.0	0.0	0.0	0.0	0.0	459.0	0.0	374.0	1050.0	5436.0
2025-08-16 23:00:00+00:00	0.0	-14158.0	239.0	0.0	0.0	2.0	686.0	0.0	0.0	857.0	...	69.0	0.0	0.0	0.0	0.0	503.0	0.0	375.0	954.0	5420.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
2025-08-24 21:00:00+00:00	0.0	-14530.0	227.0	0.0	0.0	8.0	2421.0	0.0	0.0	1533.0	...	69.0	0.0	0.0	0.0	0.0	1690.0	0.0	379.0	949.0	3453.0
2025-08-24 21:15:00+00:00	0.0	-14486.0	227.0	0.0	0.0	4.0	2374.0	0.0	0.0	1466.0	...	69.0	0.0	0.0	0.0	0.0	1062.0	0.0	378.0	947.0	3550.0
2025-08-24 21:30:00+00:00	0.0	-14306.0	227.0	0.0	0.0	4.0	2240.0	0.0	0.0	1412.0	...	69.0	0.0	0.0	0.0	0.0	1011.0	0.0	379.0	1005.0	3627.0
2025-08-24 21:45:00+00:00	0.0	-14338.0	226.0	0.0	0.0	0.0	2022.0	0.0	0.0	1341.0	...	69.0	0.0	0.0	0.0	0.0	1029.0	0.0	382.0	1011.0	3709.0
2025-08-24 22:00:00+00:00	NaN	NaN	226.0	0.0	0.0	0.0	2022.0	0.0	0.0	1341.0	...	69.0	0.0	0.0	0.0	0.0	1029.0	0.0	382.0	1011.0	3709.0

	demand	Hydro Pumped Storage	net_demand
2025-08-16 22:00:00+00:00	40444.0	0.0	40444.0
2025-08-16 22:15:00+00:00	39275.0	2.0	39277.0
2025-08-16 22:30:00+00:00	38017.0	18.0	38035.0
2025-08-16 22:45:00+00:00	37060.0	26.0	37086.0
2025-08-16 23:00:00+00:00	37242.0	158.0	37400.0
...	...	...	...
2025-08-24 21:00:00+00:00	40287.0	1.0	40288.0
2025-08-24 21:15:00+00:00	39471.0	1.0	39472.0
2025-08-24 21:30:00+00:00	39617.0	1.0	39618.0
2025-08-24 21:45:00+00:00	38791.0	1.0	38792.0
2025-08-24 22:00:00+00:00	38791.0	NaN	NaN

	Hydro Pumped Storage
	Actual Aggregated	Actual Consumption
2025-08-17 00:00:00+02:00	2172.0	0.0
2025-08-17 00:15:00+02:00	994.0	2.0
2025-08-17 00:30:00+02:00	530.0	18.0
2025-08-17 00:45:00+02:00	459.0	26.0
2025-08-17 01:00:00+02:00	503.0	158.0
...	...	...
2025-08-24 22:45:00+02:00	2002.0	52.0
2025-08-24 23:00:00+02:00	1690.0	1.0
2025-08-24 23:15:00+02:00	1062.0	1.0
2025-08-24 23:30:00+02:00	1011.0	1.0
2025-08-24 23:45:00+02:00	1029.0	1.0

	Hydro Pumped Storage	Solar	Wind Onshore
	Actual Consumption	Actual Consumption	Actual Consumption
2025-08-17 00:00:00+02:00	15.0	0.0	0.0
2025-08-17 00:15:00+02:00	88.0	0.0	0.0
2025-08-17 00:30:00+02:00	21.0	0.0	0.0
2025-08-17 00:45:00+02:00	32.0	0.0	0.0
2025-08-17 01:00:00+02:00	32.0	0.0	0.0
...	...	...	...
2025-08-24 22:45:00+02:00	8.0	0.0	0.0
2025-08-24 23:00:00+02:00	23.0	0.0	0.0
2025-08-24 23:15:00+02:00	105.0	0.0	0.0
2025-08-24 23:30:00+02:00	105.0	0.0	0.0
2025-08-24 23:45:00+02:00	335.0	0.0	0.0

ENTSO-E Electricity Data Analysis¶

Imports¶

Load Processed Data¶

Analysis and Visualization¶

Levelized cost of Heat/Hydrogen¶

Hydrogen levelized cost¶

fix me¶

	exports	Hydro Pumped Storage	Solar	Wind Onshore	imports	biomass	brown_coal	coal_gas	demand	demand_forecast	...	wind_offshore_forecast	wind_onshore	wind_onshore_forecast	hour	doy	doy_season	dt	wind_forecast	vre_forecast	residual
2025-08-16 22:00:00+00:00	-3157.0	-15.0	-0.0	-0.0	7101.0	3824.0	6345.0	474.0	39702.0	40436.0	...	4385.0	11065.0	12005.0	22	228	108	2025-08-16 22:00:00+00:00	4385.0	4385.0	35317.0
2025-08-16 22:15:00+00:00	-3295.0	-88.0	-0.0	-0.0	7348.0	3813.0	6347.0	488.0	39010.0	39815.0	...	4357.0	10948.0	11660.0	22	228	108	2025-08-16 22:15:00+00:00	4357.0	4357.0	34653.0
2025-08-16 22:30:00+00:00	-3198.0	-21.0	-0.0	-0.0	7648.0	3812.0	6337.0	481.0	38817.0	39397.0	...	4318.0	10791.0	11317.0	22	228	108	2025-08-16 22:30:00+00:00	4318.0	4318.0	34499.0
2025-08-16 22:45:00+00:00	-3014.0	-32.0	-0.0	-0.0	7477.0	3811.0	6342.0	488.0	38140.0	38968.0	...	4283.0	10653.0	11004.0	22	228	108	2025-08-16 22:45:00+00:00	4283.0	4283.0	33857.0
2025-08-16 23:00:00+00:00	-2750.0	-32.0	-0.0	-0.0	7195.0	3808.0	6344.0	492.0	37712.0	38496.0	...	4245.0	10511.0	10678.0	23	228	108	2025-08-16 23:00:00+00:00	4245.0	4245.0	33467.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
2025-08-24 21:00:00+00:00	-1028.0	-23.0	-0.0	-0.0	8869.0	3892.0	8099.0	427.0	42181.0	42582.0	...	1700.0	6436.0	6614.0	21	236	116	2025-08-24 21:00:00+00:00	1700.0	1700.0	40481.0
2025-08-24 21:15:00+00:00	-995.0	-105.0	-0.0	-0.0	9339.0	3890.0	8046.0	431.0	41816.0	42077.0	...	1700.0	6563.0	6597.0	21	236	116	2025-08-24 21:15:00+00:00	1700.0	1700.0	40116.0
2025-08-24 21:30:00+00:00	-934.0	-105.0	-0.0	-0.0	9450.0	3891.0	8029.0	448.0	41434.0	41484.0	...	1696.0	6549.0	6575.0	21	236	116	2025-08-24 21:30:00+00:00	1696.0	1696.0	39738.0
2025-08-24 21:45:00+00:00	-826.0	-335.0	-0.0	-0.0	9843.0	3883.0	7914.0	459.0	41358.0	40993.0	...	1693.0	6657.0	6555.0	21	236	116	2025-08-24 21:45:00+00:00	1693.0	1693.0	39665.0
2025-08-24 22:00:00+00:00	NaN	NaN	NaN	NaN	NaN	3883.0	7914.0	459.0	41358.0	40993.0	...	1693.0	6657.0	6555.0	22	236	116	2025-08-24 22:00:00+00:00	1693.0	1693.0	39665.0

	exports	Energy storage	Fossil Hard coal	Fossil Oil	Hydro Pumped Storage	Wind Offshore	imports	biomass	brown_coal	coal_gas	...	wind_offshore_forecast	wind_onshore	wind_onshore_forecast	hour	doy	doy_season	dt	wind_forecast	vre_forecast	residual
2025-08-16 22:00:00+00:00	-15078.0	-3.0	-1.0	NaN	-0.0	NaN	0.0	228.0	0.0	0.0	...	1214.0	5612.0	6918.0	22	228	108	2025-08-16 22:00:00+00:00	1214.0	1214.0	39230.0
2025-08-16 22:15:00+00:00	-14432.0	-3.0	-1.0	NaN	-2.0	NaN	0.0	234.0	0.0	0.0	...	1214.0	5485.0	6918.0	22	228	108	2025-08-16 22:15:00+00:00	1214.0	1214.0	38061.0
2025-08-16 22:30:00+00:00	-14563.0	-5.0	-1.0	NaN	-18.0	NaN	0.0	236.0	0.0	0.0	...	1214.0	5476.0	6918.0	22	228	108	2025-08-16 22:30:00+00:00	1214.0	1214.0	36803.0
2025-08-16 22:45:00+00:00	-14567.0	-3.0	-1.0	NaN	-26.0	NaN	0.0	237.0	0.0	0.0	...	1214.0	5436.0	6918.0	22	228	108	2025-08-16 22:45:00+00:00	1214.0	1214.0	35846.0
2025-08-16 23:00:00+00:00	-14158.0	-3.0	-1.0	NaN	-158.0	NaN	0.0	239.0	0.0	0.0	...	1230.0	5420.0	6424.0	23	228	108	2025-08-16 23:00:00+00:00	1230.0	1230.0	36012.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
2025-08-24 21:00:00+00:00	-14530.0	-3.0	-1.0	NaN	-1.0	NaN	0.0	227.0	0.0	0.0	...	700.0	3453.0	3320.0	21	236	116	2025-08-24 21:00:00+00:00	700.0	700.0	39587.0
2025-08-24 21:15:00+00:00	-14486.0	-5.0	-1.0	NaN	-1.0	NaN	0.0	227.0	0.0	0.0	...	700.0	3550.0	3320.0	21	236	116	2025-08-24 21:15:00+00:00	700.0	700.0	38771.0
2025-08-24 21:30:00+00:00	-14306.0	-5.0	-1.0	NaN	-1.0	NaN	0.0	227.0	0.0	0.0	...	700.0	3627.0	3320.0	21	236	116	2025-08-24 21:30:00+00:00	700.0	700.0	38917.0
2025-08-24 21:45:00+00:00	-14338.0	-20.0	-1.0	NaN	-1.0	NaN	0.0	226.0	0.0	0.0	...	700.0	3709.0	3320.0	21	236	116	2025-08-24 21:45:00+00:00	700.0	700.0	38091.0
2025-08-24 22:00:00+00:00	NaN	NaN	NaN	NaN	NaN	NaN	NaN	226.0	0.0	0.0	...	700.0	3709.0	3320.0	22	236	116	2025-08-24 22:00:00+00:00	700.0	700.0	38091.0